/*
* 客户管理模块
*/
const express = require('express');
const router = express.Router();
// 引入数据库
const db = require("../../../config/db");
// 引入时间包
const moment = require("moment");
// 引入方法库
const { toCamelCase, toUnderlineCase, verifyToken} = require("../../../config/utils");
// 引入日志
const log = require("../../log");

// 获取客户列表
router.get("/list", (req, res)=>{
    console.log("===========获取客户列表接口============");
    // 获取参数
    let {pageNum, pageSize, clientCode, clientName, clientNick, clientEn, enableFlag} = req.query;
    // 拼接sql语句
    let sql = "select * from md_client";
    let sql_where = ` where 1=1`;
    if(clientCode){
        sql_where += ` and client_code like '%${clientCode}%'`;
    }
    if(clientName){
        sql_where += ` and client_name like '%${clientName}%'`;
    }
    if(clientNick){
        sql_where += ` and client_nick like '%${clientNick}%'`;
    }
    if(clientEn){
        sql_where += ` and client_en like '%${clientEn}%'`;
    }
    if(enableFlag){
        sql_where += ` and enable_flag = '${enableFlag}'`;
    }
    if(clientCode || clientName || clientNick || clientEn || enableFlag){
        sql += sql_where;
    }
    
    // 分页
    sql += ` limit ${(pageNum-1)*pageSize}, ${pageSize}`;
    // 查询数据库
    db.query("查询客户列表", sql, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询客户列表失败", data: err});
            return;
        }
        // 查询总数
        db.query("查询客户总数", `select count(*) as count from md_client ${sql_where}`, (err, count)=>{
            if(err){
                res.send({code: 500, msg: "查询客户总数失败", data: err});
                return;
            }
            if(result.length > 0){
                 // 变量Json数组key转成下划线第一个大写
                 result = result.map(item => {
                    return toCamelCase(item)
                })
                res.send({code: 200, msg: "查询客户列表成功", rows: result, total: count[0].count});
            }else{
                res.send({code: 200, msg: "查询客户列表成功", data: [], total: 0});
            }
        })
    })
})

// 新增客户
router.post("/", (req, res)=>{
    console.log("===========新增客户接口============");
    // 获取参数
    let {address, clientCode, clientDes, clientEn, clientLogo, clientName, clientNick, clientType, contact1, contact1Email, contact1Tel, contact2, contact2Email, contact2Tel, creditCode, email, enableFlag, remark, tel, website} = req.body;
    // 获取创建人
    let {username} = verifyToken(req.headers.authorization);
    // 获取创建时间
    let createTime = moment().format("YYYY-MM-DD HH:mm:ss");
    // 判断客户编码和客户名称是否重复
    db.query("查询客户编码和客户名称是否重复", `select * from md_client where client_code = '${clientCode}' or client_name = '${clientName}'`, (err, sql_check_result)=>{
        if(err){
            res.send({code: 500, msg: "查询客户编码和客户名称是否重复失败", data: err});
            return;
        }
        if(sql_check_result.length > 0){
            res.send({code: 500, msg: "客户编码或客户名称已存在"});
            return;
        }
        // 插入数据库
        let sql = `insert into md_client (address, client_code, client_des, client_en, client_logo, client_name, client_nick, client_type, contact1, 
            contact1_email, contact1_tel, contact2, contact2_email, contact2_tel, credit_code, email, enable_flag, remark, tel, website, create_by, create_time)
        values (${address ? "'"+address+"'" : null}, '${clientCode}', ${clientDes ? "'"+clientDes+"'" : null}, ${clientEn ? "'"+clientEn+"'" : null}, 
        ${clientLogo ? "'"+clientLogo+"'" : null}, '${clientName}', ${clientNick ? "'"+clientNick+"'" : null}, ${clientType ? "'"+clientType+"'" : null}, 
        ${contact1 ? "'"+contact1+"'" : null}, ${contact1Email ? "'"+contact1Email+"'" : null}, ${contact1Tel ? "'"+contact1Tel+"'" : null}, 
        ${contact2 ? "'"+contact2+"'" : null}, ${contact2Email ? "'"+contact2Email+"'" : null}, ${contact2Tel ? "'"+contact2Tel+"'" : null}, 
        ${creditCode ? "'"+creditCode+"'" : null}, ${email ? "'"+email+"'" : null}, ${enableFlag ? "'"+enableFlag+"'" : null}, ${remark ? "'"+remark+"'" : null},
        ${tel ? "'"+tel+"'" : null}, ${website ? "'"+website+"'" : null}, '${username}', '${createTime}')`;
        db.query("新增客户", sql, (err, result)=>{
            if(err){
                res.send({code: 500, msg: "新增客户失败", data: err});
                return;
            }
            if(result.affectedRows > 0){
                res.send({code: 200, msg: "新增客户成功"});
            }else{
                res.send({code: 500, msg: "新增客户失败"});
            }
        })
    })
})

// 查询单个客户
router.get("/:id", (req, res)=>{
    console.log("===========查询单个客户接口============");
    // 获取参数
    let id = req.params.id;
    db.query("查询单个客户", `select * from md_client where client_id = ${id}`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询单个客户失败", data: err});
            return;
        }
        if(result.length > 0){
            // 变量Json数组key转成下划线第一个大写
            result = result.map(item => {
                return toCamelCase(item)
            })
            res.send({code: 200, msg: "查询单个客户成功", data: result[0]});
        }else{
            res.send({code: 500, msg: "查询单个客户失败"});
        }
    })
})

// 修改客户
router.put("/", (req, res)=>{
    console.log("===========修改客户接口============");
    // 获取参数
    let {clientId, address, clientCode, clientDes, clientEn, clientLogo, clientName, clientNick, clientType, contact1, contact1Email, contact1Tel, contact2, contact2Email, contact2Tel, creditCode, email, enableFlag, remark, tel, website} = req.body;
    // 获取更新人
    let {username} = verifyToken(req.headers.authorization);
    // 获取更新时间
    let updateTime = moment().format("YYYY-MM-DD HH:mm:ss");
    // 查询客户名称是否重复
    db.query("查询客户名称是否重复", `select * from md_client where client_name = '${clientName}' and client_id != ${clientId}`, (err, result)=>{
        if(err){
            res.send({code: 500, msg: "查询客户名称是否重复失败", data: err});
            return;
        }
        if(result.length > 0){
            res.send({code: 500, msg: "客户名称已存在"});
            return;
        }
        // 生成更新sql语句
        let sql = `update md_client set address = ${address ? "'"+address+"'" : null}, client_des = ${clientDes ? "'"+clientDes+"'" : null}, 
        client_en = ${clientEn ? "'"+clientEn+"'" : null}, client_logo = ${clientLogo ? "'"+clientLogo+"'" : null}, client_name = '${clientName}',
        client_nick = ${clientNick ? "'"+clientNick+"'" : null}, client_type = ${clientType ? "'"+clientType+"'" : null}, contact1 = ${contact1 ? "'"+contact1+"'" : null},
        contact1_email = ${contact1Email ? "'"+contact1Email+"'" : null}, contact1_tel = ${contact1Tel ? "'"+contact1Tel+"'" : null}, contact2 = ${contact2 ? "'"+contact2+"'" : null},
        contact2_email = ${contact2Email ? "'"+contact2Email+"'" : null}, contact2_tel = ${contact2Tel ? "'"+contact2Tel+"'" : null}, credit_code = ${creditCode ? "'"+creditCode+"'" : null},
        email = ${email ? "'"+email+"'" : null}, enable_flag = ${enableFlag ? "'"+enableFlag+"'" : null}, remark = ${remark ? "'"+remark+"'" : null},
        tel = ${tel ? "'"+tel+"'" : null}, website = ${website ? "'"+website+"'" : null}, update_by = '${username}', update_time ='${updateTime}' 
        where client_id = '${clientId}'`
        // 执行更新操作
        db.query("更新客户信息", sql, (err, result)=>{
            if(err){
                res.send({code: 500, msg: "更新客户信息失败", data: err});
                return;
            }
            if(result.affectedRows > 0){
                res.send({code: 200, msg: "更新客户信息成功", data: result});
            }else{
                res.send({code: 500, msg: "更新客户信息失败"});
            }
            
        })
    })
})

// 删除客户
router.delete("/:id", (req, res) => {
    console.log("===========删除客户接口============");
    // 获取参数
    let {id} = req.params;
     // 拼接sql,删除多条数据
     let sql = `delete from md_client where client_id in(${id})`;
    // 查询数据库
    db.query("删除客户", sql, (err, result) => {
        if (err) {
            res.send({code: 500, msg: "删除客户失败", data: err});
            return
        }
        if(result.affectedRows > 0){
            res.send({code: 200, msg: "删除客户成功"})
        }else{
            res.send({code: 500, msg: "删除客户失败"})
        }
    })
})

module.exports = router;
